//produce db relating to migrants recruited by agencies  

clear
set memory 500m
set matsize 100
set more off 
# delimit;

* Set directory; 

cd "~/Desktop/migrec_replication/do/";

** append final datasets; 



foreach i of numlist 5/15 {; 

insheet using ../raw/final-`i'.csv, comma clear; 

cap tostring cn, replace; 
destring an, force replace;

save ../dta/final-`i'.dta, replace; 

};


use ../dta_secure/final-5.dta,  clear; 

foreach i of numlist 6/15 {; 

append using ../dta_secure/final-`i'.dta, force; 

};


use ../dta/final-5.dta, clear;

save ../dta_secure/final.dta, replace;


** set of things to figure out 



** STEP 1: get rid of troublesome passports; 

drop if ppno == ""; 
drop if substr(ppno,1,1) == "." |  substr(ppno,1,1) == "-" | 
substr(ppno,1,1) == "," |  substr(ppno,1,1) == "0" |  substr(ppno,1,1) == "4"; 


** STEP 2: figure out dates; 

gen departure_year = substr(dep_date,-7,2); 
replace departure_year = "20" + departure_year; 

gen departure_month = substr(dep_date,1,2); 
gen rep_m = substr(dep_date,1,1); 
replace departure_month = rep_m if substr(departure_month,-1,1) == "/" ;


save ../dta_secure/final.dta, replace; 


** STEP 3: figure out countries ;


gen zeros = ""; 

destring cn, force gen(cat); 

tostring cat, force gen(cn_code); 

replace zeros = "00" if cat > 0 & cat <= 9;
replace zero = "0" if cat > 9 & cat < 100; 

replace cn_code = zeros + cn_code; 



merge m:1 cn_code using ../dta_secure/country.dta, gen(country_code) force;
keep if country_code != 2; 


** STEP 4: figure out villages; 

rename town_code towncode; 

merge m:1 towncode using ../dta_secure/villages.dta, gen(townmerge);

keep if townmerge != 2; 



** STEP 5: figure out sectors; 


rename jobcate job_code; 

merge m:1 job_code using ../dta_secure/job_codes, gen(jobcodemerge); 

keep if jobcodemerge!= 2; 


* note, using jobcode_sector, this is the meaning of the codes: 

H - Hospitality 
C - Construction
D - Domestic
G - Garments
N - n/a 

;


** STEP 6: Convert currencies ;

*note, does not account for inflation;

merge m:1 cuunit using ../dta_secure/currencies.dta, gen(currencymerge); 

keep if currencymerge != 2; 

gen usd_salary = ((salary*cn_currency_rate)/132.9);


* step 6.5: deflate currencies; 


merge m:1 cn_name departure_year using ../dta_secure/currency_deflated, gen(_curmerge) ; 

keep if _curmerge != 2; 

gen usd_salary_d = salary/real_value; 

label var usd_salary_d "salary in usd, constant 2015 dollars";


** STEP 7: Map to local agencies;


merge m:1 an using ../dta_secure/agencies, gen(agencymerge2) force;

keep if agencymerge2 != 2 ;



** STEP 8: Figure out age; 

gen birth_yr = substr(birth_day, -7,2); 

destring birth_yr, force replace; 

label var birth_yr "year of birth"; 

replace birth_yr = birth_yr + 1900; 

gen current_age = 2016-birth_yr; 

label var current_age "current age";


save ../dta_secure/final.dta, replace; 


** ADD ANF JOB SECTOR CLASSIFICATIONS; 

merge m:1 jobcode_jobsum using ../res/jobcode_classification_anf.dta, gen(jobcode_class_anf);



** MISC; 

* clean up years; 

 keep if departure_year >= "2005" & departure_year <= "2015" ;
 
 
 save ../dta_secure/final.dta, replace; 



 
 
 
 












